Strategy ONE

Create a Derived Metric from Scratch Using the Metric Formula Editor

Creating a derived metric from scratch allows you to customize the metric and make it more sophisticated to fit your needs. You can perform the following tasks:

  • Build custom expressions, such as percent-to-total metrics or expressions that use custom functions created with the MicroStrategy Function Plug-In Wizard. See the Functions Reference for more information.
  • Create a metric by typing the metric formula directly. You can add conditional calculations, to create groups of data by filtering metric values. In addition, you can group regions into poor revenue producers and good revenue producers. This supplements the conditional analysis provided by filters and thresholds.
  • Perform statistical analysis by creating derived metrics based on R analytics.

To create a metric, you must define the metric's formula, which consists of arithmetic operators (+, -, *, and /) and functions. The operators and functions can be applied to attributes or metrics. An example of the formula of a compound metric is RunningAvg(Cost), where Cost is a metric and RunningAvg is a function that calculates the running average. Another example is Sum(Cost) + Sum(Profit), where Cost and Profit are metrics and Sum is a function that calculates the sum. In this case, the summation of the Cost metric is added to the summation of the Profit metric.

You can create a nested metric, where one aggregation function is enclosed inside another. A sample metric formula is Avg(Sum(Revenue)). The inner function Sum calculates the total for the Revenue fact, and the outer function Avg calculates the average of that result. If you want to know the average revenue per category for every region, you can create a nested metric that first calculates the revenue for all categories, and then calculates the average for each region. The metric is defined as: Avg(Sum(Revenue) {~, Category}) {~, Region}

The inner formula, Sum(Revenue), calculates the revenue at the Category level. The outer formula then calculates the average of the resulting values at the Region level.

To add functions to the metric formula, you must select any options required to define the function. If you want to add a grouping function, such as Sum, Average, First, or Max, you must define the function's expression, and you can also change the attribute level that the metric aggregates at. If you want to add a non-grouping function, such as data mining, date, OLAP, and ranking functions, you must define the input values (called arguments) for the function, as well as any parameters you can use to determine the behavior of the function.

See in the Advanced Reporting Help for the syntax of metric formulas that use grouping functions. See the Functions Reference for the syntax of non-grouping functions.

  1. Open an existing document.
  2. In the Dataset Objects panel, right click a dateset, and select Insert New Metric.
  3. Hover over a function in the Functions panel and click Edit.
  4. Complete the required fields in the dialog and click Add.
  5. To see more detailed information about the selected function and its arguments, click Details in the bottom pane.
  6. To manually define a derived metric, you can type its definition in the text box in the right pane.
  7. Enter a name for your new metric attribute in Metric Name.
  8. To define aggregation and subtotal behavior, click Metric Options in the right pane.
  9. Click Save.

Aggregation and subtotal behavior is automatically determined by default. You can change that behavior, to specify whether the derived metric is calculated using the whole dataset or calculated using the data in the visualization that it is placed on. See Change the Aggregation and Subtotal Behavior for a Derived Metric for more information.